package networkowner;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;


import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

public class DerbyDao {

    private JdbcTemplate template;

    public DerbyDao() {
        // Initialize the datasource, could /should be done of Spring
        // configuration
        try {
            Context ctx = new InitialContext();
            DataSource ds = (DataSource) ctx.lookup("jdbc/freesql");
            template = new JdbcTemplate(ds);
        } catch (NamingException ex) {
        }
    }

    public int insertRecievedMessage(final String body) {
        final String sql = "insert into RECIEVED_MESSAGES(body) values (?)";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        template.update(
                new PreparedStatementCreator() {

                    public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                        PreparedStatement ps =
                                connection.prepareStatement(sql, new String[]{"id"});
                        ps.setString(1, body);
                        return ps;
                    }
                },
                keyHolder);

        return keyHolder.getKey().intValue();
    }

    public int insertComposedMessage(final String body) {
        final String sql = "insert into COMPOSED_MESSAGES(body) values (?)";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        template.update(
                new PreparedStatementCreator() {

                    public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                        PreparedStatement ps =
                                connection.prepareStatement(sql, new String[]{"id"});
                        ps.setString(1, body);
                        return ps;
                    }
                },
                keyHolder);

        return keyHolder.getKey().intValue();
    }

    boolean clientHasContract(String clientRegNum, Date contractStartDate) {
        String sql = "select count(*) from CONTRACT cont join CLIENT clie on cont.CLIENT_ID=clie.ID where clie.REG_NR=? and cont.START_DATE=?";
        return template.queryForInt(sql, clientRegNum, contractStartDate) > 0;
    }

    boolean isProviderInDb(String providerRegNum) {
        String sql = "select count(*) from SUPPLIER where reg_num=?";
        return template.queryForInt(sql, providerRegNum) > 0;
    }

    boolean isClientInDb(String clientRegNum) {
        String sql = "select count(*) from CLIENT where reg_num=?";
        return template.queryForInt(sql, clientRegNum) > 0;
    }
}